package com.geekstudy.week8;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Week8Work2 {

    final int DATABASE_SIZE = 2;
    final int TABLE_PER_DATABASE_SIZE = 16;

    List<Connection> connectionList = new ArrayList<Connection>();

    private Connection connection(String url, String username, String password) throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    public Connection connection1() throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/geek_java4_1";
        String username = "java4";
        String password = "java4";
        if(connectionList.size() == 0) {
            Connection connection = connection(url, username, password);
            connectionList.add(connection);
        }

        return connectionList.get(0);
    }

    public Connection connection2() throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/geek_java4_2";
        String username = "java4";
        String password = "java4";
        if(connectionList.size() == 1) {
            Connection connection = connection(url, username, password);
            connectionList.add(connection);
        }
        return connectionList.get(1);
    }

    public void createTable(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        for(int size = 1; size <= TABLE_PER_DATABASE_SIZE; size++) {
            String tableName = "order_" + size;
            boolean dropResult = statement.execute("drop table if exists " + tableName);
            String tableSql = "create table order_" + size
                    + "(id varchar(64) primary key not null comment '主键', price decimal(20,6) comment '价格')";
            boolean insertResult = statement.execute(tableSql);
            System.out.println(" catalog:" + connection.getCatalog() + " schema:" + connection.getSchema()
                    + tableName + " drop:" + dropResult + " insert:" + insertResult);
        }
        statement.close();

    }

    public int databaseIndex(String id) {
        String database = id.substring(0, 1);
        int index = Integer.valueOf(database) % DATABASE_SIZE;
        if(index == 0) {
            index = DATABASE_SIZE;
        }
        index = index - 1;
        return index;
    }

    public String tableName(String id) {
        String tableSuffix = id.substring(id.length() - 2, id.length());
        int numSuffix = Integer.valueOf(tableSuffix) % TABLE_PER_DATABASE_SIZE;
        if(numSuffix == 0) {
            numSuffix = TABLE_PER_DATABASE_SIZE;
        }
        String tableName = "order_" + numSuffix;
        return tableName;
    }



    public void insert(String id, BigDecimal price) throws SQLException {
        int databaseIndex = databaseIndex(id);
        Connection connection = connectionList.get(databaseIndex);

        String tableName = tableName(id);
        String sql = "insert " + tableName + " (id, price) values('" + id + "', '" + price + "' )";
        Statement statement = connection.createStatement();
        boolean insertResult = statement.execute(sql);
        System.out.println("insert database index: " + databaseIndex + " table:" + tableName + " id:" + id + " insertResult:" + insertResult);
    }

    public void query(String id) throws SQLException {
        int databaseIndex = databaseIndex(id);
        Connection connection = connectionList.get(databaseIndex);
        String tableName = tableName(id);
        String sql = "select id, price from " + tableName + " where id='" + id + "' ";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if(resultSet.next()) {
            BigDecimal price = resultSet.getBigDecimal("price");
            System.out.println(" databaseIndex:" + databaseIndex + " tableName:" + tableName
                    + " id:" + id + " price:" + price);
        }

    }




    public static void main(String[] args) {

        try {
            Week8Work2 work2 = new Week8Work2();

            Connection connection1 = work2.connection1();
            Connection connection2 = work2.connection2();

            work2.createTable(connection1);
            work2.createTable(connection2);

            String id1 = "11021123401";
            work2.insert(id1, new BigDecimal("12.23"));

            String id2 = "2310000116";
            work2.insert(id2, new BigDecimal("31.21"));


            work2.query(id1);
            work2.query(id2);




        } catch(Exception e) {
            e.printStackTrace();
        }

    }





}
